const Base = require('./base.js');

module.exports = class extends Base {
  // -----------------------------------------------------------------------------员工类通用接口
  // 获取我的权限下所有的员工数量接口
  async getworkermanagenum(floor, jsonshop, ismanage, departmentid, datatype) {    
    switch (floor) {
      case '0':
        if (datatype == 1) {
          var info = await this.model().query(' SELECT IFNULL(count(id),0) as allnum FROM `think_or_app_user` WHERE ( shopid in (' + jsonshop + ') or departmentid = ' + departmentid + ' ) AND isdisable = 0 ');
        } else {
          var info = await this.model().query(' SELECT IFNULL(count(id),0) as allnum FROM `think_or_app_user` WHERE shopid in (' + jsonshop + ')  AND isdisable = 0 ');
        }
        break;
      case '1':
        var info = await this.model().query(' SELECT IFNULL(count(id),0) as allnum FROM `think_or_app_user` WHERE shopid = ' + jsonshop + ' AND isdisable = 0 ');
        break;
      case '2':
        var info = await this.model().query(' SELECT IFNULL(count(id),0) as allnum FROM `think_or_app_user` WHERE bumenid = ' + jsonshop + ' AND isdisable = 0 ');
        break;
      case '3':
        if (ismanage == 1) {
          var info = await this.model().query(' SELECT IFNULL(count(id),0) as allnum FROM `think_or_app_user` WHERE xiaozuid = ' + jsonshop + ' AND isdisable = 0 ');
        }
        break;
    }
    if (think.isEmpty(info)) { return 0 } else { return info[0].allnum }
  }
  // 获取我管理的员工出勤人数通用接口
  async getworkerworkonnum(starttime, endtime, floor, jsonshop, ismanage, departmentid, datatype) {
    switch (floor) {
      case '0':
        if (datatype == 1) {
          var info = await this.model().query(' SELECT IFNULL(count(ouw.id),0) as num FROM `think_or_user_work` ouw LEFT JOIN `think_or_app_user` au ON ouw.userid = au.id WHERE ouw.time_start >= ' + starttime + ' AND  ouw.time_start < ' + endtime + ' AND (ouw.type = 1 or ouw.type = 4 or ouw.type = 7 or ouw.type = 6) AND (au.shopid in (' + jsonshop + ') or au.departmentid = ' + departmentid + ' ) ');
        } else {
          var info = await this.model().query(' SELECT IFNULL(count(ouw.id),0) as num FROM `think_or_user_work` ouw LEFT JOIN `think_or_app_user` au ON ouw.userid = au.id WHERE ouw.time_start >= ' + starttime + ' AND  ouw.time_start < ' + endtime + ' AND (ouw.type = 1 or ouw.type = 4 or ouw.type = 7 or ouw.type = 6) AND au.shopid in (' + jsonshop + ') ');
        }
        break;
      case '1':
        var info = await this.model().query(' SELECT IFNULL(count(ouw.id),0) as num FROM `think_or_user_work` ouw LEFT JOIN `think_or_app_user` au ON ouw.userid = au.id WHERE ouw.time_start >= ' + starttime + ' AND  ouw.time_start < ' + endtime + ' AND (ouw.type = 1 or ouw.type = 4 or ouw.type = 7 or ouw.type = 6) AND au.shopid = ' + jsonshop + ' ');
        break;
      case '2':
        var info = await this.model().query(' SELECT IFNULL(count(ouw.id),0) as num FROM `think_or_user_work` ouw LEFT JOIN `think_or_app_user` au ON ouw.userid = au.id WHERE ouw.time_start >= ' + starttime + ' AND  ouw.time_start < ' + endtime + ' AND (ouw.type = 1 or ouw.type = 4 or ouw.type = 7 or ouw.type = 6) AND au.bumenid = ' + jsonshop + '');
        break;
      case '3':
        if (ismanage == 1) {
          var info = await this.model().query(' SELECT IFNULL(count(ouw.id),0) as num FROM `think_or_user_work` ouw LEFT JOIN `think_or_app_user` au ON ouw.userid = au.id WHERE ouw.time_start >= ' + starttime + ' AND  ouw.time_start < ' + endtime + ' AND (ouw.type = 1 or ouw.type = 4 or ouw.type = 7 or ouw.type = 6) AND au.xiaozuid = ' + jsonshop + '');
        }
        break;
    }
    if (think.isEmpty(info)) { return 0 } else { return info[0].num }
  }
  // 获取我管理的员工有出单员工的数量通用接口
  async getworkersalenum(starttime, endtime, floor, jsonshop, ismanaged) {
    switch (floor) {
      case '0':
        var info = await this.model().query('SELECT count(*) as num FROM ( SELECT * FROM `think_or_shop_yejiticheng` WHERE addtime >= ' + starttime + ' AND addtime < ' + endtime + ' AND shopid IN (' + jsonshop + ') GROUP BY userid ) as A');
        break;
      case '1':
        var info = await this.model().query('SELECT count(*) as num FROM ( SELECT * FROM `think_or_shop_yejiticheng` WHERE addtime >= ' + starttime + ' AND addtime < ' + endtime + ' AND shopid = ' + jsonshop + ' GROUP BY userid ) as A');
        break;
      case '2':
        var info = await this.model().query('SELECT count(*) as num FROM ( SELECT * FROM `think_or_shop_yejiticheng` WHERE addtime >= ' + starttime + ' AND addtime < ' + endtime + ' AND bumenid = ' + jsonshop + ' GROUP BY userid ) as A');
        break;
      case '3':
        if (ismanage == 1) {
          var info = await this.model().query('SELECT count(*) as num FROM ( SELECT * FROM `think_or_shop_yejiticheng` WHERE addtime >= ' + starttime + ' AND addtime < ' + endtime + ' AND xiaozuid = ' + jsonshop + ' GROUP BY userid ) as A');
        }
        break;
    }

    if (think.isEmpty(info)) { return 0 } else { return info[0].num }
  }
  // 获取我的时间内未完成的服务记录
  async getworkfuwunum(starttime, endtime, floor, jsonshop, ismanage, user_id) {
    switch (floor) {
      case '0':
      case '1':
        var info = await this.model().query('SELECT IFNULL(count(id),0) as num FROM `think_or_shop_yejiticheng` WHERE addtime >= ' + starttime + ' AND addtime < ' + endtime + ' AND shopid IN (' + jsonshop + ') AND fuwujilu = 0 AND guestid <> 0 AND guestid <> 1 ');
        break;
      case '2':
        var info = await this.model().query('SELECT IFNULL(count(id),0) as num FROM `think_or_shop_yejiticheng` WHERE addtime >= ' + starttime + ' AND addtime < ' + endtime + ' AND bumenid = ' + jsonshop + ' AND fuwujilu = 0 AND guestid <> 0 AND guestid <> 1');
        break;
      case '3':
        if (ismanage == 1) {
          var info = await this.model().query('SELECT IFNULL(count(id),0) as num FROM `think_or_shop_yejiticheng` WHERE addtime >= ' + starttime + ' AND addtime < ' + endtime + ' AND xiaozuid = ' + jsonshop + ' AND fuwujilu = 0 AND guestid <> 0 AND guestid <> 1');
        } else {
          var info = await this.model().query('SELECT IFNULL(count(id),0) as num FROM `think_or_shop_yejiticheng` WHERE addtime >= ' + starttime + ' AND addtime < ' + endtime + ' AND userid = ' + user_id + ' AND fuwujilu = 0 AND guestid <> 0 AND guestid <> 1');
        }
        break;
    }

    if (think.isEmpty(info)) { return 0 } else { return info[0].num }
  }
  // 获取我的时间内未完成的回访记录
  async getworkhuifangnum(starttime, endtime, floor, jsonshop, ismanage, user_id) {
    switch (floor) {
      case '0':
      case '1':
        var info = await this.model().query('SELECT IFNULL(count(id),0) as num FROM `think_or_shop_yejiticheng` WHERE addtime >= ' + starttime + ' AND addtime < ' + endtime + ' AND shopid IN (' + jsonshop + ') AND huifangjilu = 0 ');
        break;
      case '2':
        var info = await this.model().query('SELECT IFNULL(count(id),0) as num FROM `think_or_shop_yejiticheng` WHERE addtime >= ' + starttime + ' AND addtime < ' + endtime + ' AND bumenid = ' + jsonshop + ' AND huifangjilu = 0 ');
        break;
      case '3':
        if (ismanage == 1) {
          var info = await this.model().query('SELECT IFNULL(count(id),0) as num FROM `think_or_shop_yejiticheng` WHERE addtime >= ' + starttime + ' AND addtime < ' + endtime + ' AND xiaozuid = ' + jsonshop + ' AND huifangjilu = 0 ');
        } else {
          var info = await this.model().query('SELECT IFNULL(count(id),0) as num FROM `think_or_shop_yejiticheng` WHERE addtime >= ' + starttime + ' AND addtime < ' + endtime + ' AND userid = ' + user_id + ' AND huifangjilu = 0 ');
        }
        break;
    }

    if (think.isEmpty(info)) { return 0 } else { return info[0].num }
  }
  // 获取我的未完成的日程数量
  async getworkermynomanageday(year, month, day, user_id) {
    const info = await this.model().query(' SELECT IFNULL(count(id),0) as num FROM `think_or_user_manage_day` WHERE year = ' + year + ' AND month = ' + month + ' AND day = ' + day + ' AND userid = ' + user_id + ' AND type = 1 ');
    if (think.isEmpty(info)) { return 0 } else { return info[0].num }
  }
  // 检查今日是否已经提交报表
  async getworkertodayisdaysheet(starttime, endtime, user_id) {
    const info = await this.model().query(' SELECT id FROM `think_or_user_daysheet` WHERE addtime >= ' + starttime + ' AND addtime < ' + endtime + ' AND userid = ' + user_id + ' ');
    if (think.isEmpty(info)) { return 0 } else { return 1 }
  }
  // -----------------------------------------------------------------------------客户类通用接口
  // 获取规划客数通用接口
  async getguestplannum(starttime, endtime, floor, jsonshop, ismanage, user_id) {
    switch (floor) {
      case '0':
      case '1':
        var info = await this.model().query(' SELECT IFNULL(count(po.guestid),0) as num FROM `think_plan_order` po LEFT JOIN `think_or_guest` og ON po.guestid = og.id LEFT JOIN `think_or_app_user` au ON po.userid = au.id WHERE po.arrivetime >= ' + starttime + ' AND po.arrivetime < ' + endtime + ' AND au.shopid IN (' + jsonshop + ') GROUP BY po.guestid ');
        break;
      case '2':
        var info = await this.model().query(' SELECT IFNULL(count(po.guestid),0) as num FROM `think_plan_order` po LEFT JOIN `think_or_guest` og ON po.guestid = og.id LEFT JOIN `think_or_app_user` au ON po.userid = au.id WHERE po.arrivetime >= ' + starttime + ' AND po.arrivetime < ' + endtime + ' AND au.bumenid = ' + jsonshop + ' GROUP BY po.guestid ');
        break;
      case '3':
        if (ismanage == 1) {
          var info = await this.model().query(' SELECT IFNULL(count(po.guestid),0) as num FROM `think_plan_order` po LEFT JOIN `think_or_guest` og ON po.guestid = og.id LEFT JOIN `think_or_app_user` au ON po.userid = au.id WHERE po.arrivetime >= ' + starttime + ' AND po.arrivetime < ' + endtime + ' AND au.xiaozuid = ' + jsonshop + ' GROUP BY po.guestid ');
        } else {
          var info = await this.model().query(' SELECT IFNULL(count(guestid),0) as num FROM `think_plan_order`  WHERE  arrivetime >= ' + starttime + ' AND arrivetime < ' + endtime + ' AND userid = ' + user_id + ' GROUP BY guestid ');
        }
        break;
    }
    if (think.isEmpty(info)) { return 0 } else { return info[0].num }
  }
  // 获取到店客数通用接口
  async getguestcomenum(starttime, endtime, floor, jsonshop, ismanage, user_id) {
    switch (floor) {
      case '0':
      case '1':
        var info = await this.model().query(' SELECT count(*) as num FROM (SELECT osy.guestid FROM `think_or_shop_yejiticheng` osy LEFT JOIN `think_or_app_user` au ON osy.userid = au.id WHERE osy.addtime >= ' + starttime + ' AND osy.addtime < ' + endtime + ' AND au.shopid IN (' + jsonshop + ') GROUP BY osy.guestid) as A ');
        break;
      case '2':
        var info = await this.model().query(' SELECT count(*) as num FROM (SELECT osy.guestid FROM `think_or_shop_yejiticheng` osy LEFT JOIN `think_or_app_user` au ON osy.userid = au.id WHERE osy.addtime >= ' + starttime + ' AND osy.addtime < ' + endtime + ' AND au.bumenid = ' + jsonshop + ' GROUP BY osy.guestid) as A ');
        break;
      case '3':
        if (ismanage == 1) {
          var info = await this.model().query(' SELECT count(*) as num FROM (SELECT osy.guestid FROM `think_or_shop_yejiticheng` osy LEFT JOIN `think_or_app_user` au ON osy.userid = au.id WHERE osy.addtime >= ' + starttime + ' AND osy.addtime < ' + endtime + ' AND au.xiaozuid = ' + jsonshop + ' GROUP BY osy.guestid) as A ');
        } else {
          var info = await this.model().query(' SELECT IFNULL(count(guestid),0) as num FROM `think_or_shop_yejiticheng` WHERE addtime >= ' + starttime + ' AND addtime < ' + endtime + ' AND userid = ' + user_id + ' GROUP BY guestid ');
        }
        break;
    }
    if (think.isEmpty(info)) { return 0 } else { return info[0].num }
  }
  // 获取预约客户的数量通用接口
  async getguestbesknum(starttime, endtime, floor, jsonshop, ismanage, user_id) {
    switch (floor) {
      case '0':
      case '1':
        var info = await this.model().query(' SELECT IFNULL(count(tb.guestid),0) as num FROM `think_bespeak` tb LEFT JOIN `think_or_app_user` au ON tb.userid = au.id WHERE tb.bespeaktime >= ' + starttime + ' AND tb.bespeaktime < ' + endtime + ' AND au.shopid IN (' + jsonshop + ') GROUP BY tb.guestid ');
        break;
      case '2':
        var info = await this.model().query(' SELECT IFNULL(count(tb.guestid),0) as num FROM `think_bespeak` tb LEFT JOIN `think_or_app_user` au ON tb.userid = au.id WHERE tb.bespeaktime >= ' + starttime + ' AND tb.bespeaktime < ' + endtime + ' AND au.bumenid = ' + jsonshop + ' GROUP BY tb.guestid ');
        break;
      case '3':
        if (ismanage == 1) {
          var info = await this.model().query(' SELECT IFNULL(count(tb.guestid),0) as num FROM `think_bespeak` tb LEFT JOIN `think_or_app_user` au ON tb.userid = au.id WHERE tb.bespeaktime >= ' + starttime + ' AND tb.bespeaktime < ' + endtime + ' AND au.xiaozuid = ' + jsonshop + ' GROUP BY tb.guestid ');
        } else {
          var info = await this.model().query(' SELECT IFNULL(count(guestid),0) as num FROM `think_bespeak` WHERE bespeaktime >= ' + starttime + ' AND bespeaktime < ' + endtime + ' AND userid = ' + user_id + ' GROUP BY guestid ');
        }
        break;
    }

    if (think.isEmpty(info)) { return 0 } else { return info[0].num }
  }
  // 获取预约到店客户的数量通用接口
  async getguestbeskarrivenum(starttime, endtime, floor, jsonshop, ismanage, user_id) {
    switch (floor) {
      case '0':
      case '1':
        var info = await this.model().query(' SELECT IFNULL(count(tb.guestid),0) as num FROM `think_bespeak` tb LEFT JOIN `think_or_app_user` au ON tb.userid = au.id WHERE tb.bespeaktime >= ' + starttime + ' AND tb.bespeaktime < ' + endtime + ' AND tb.type =2 AND au.shopid IN (' + jsonshop + ') GROUP BY tb.guestid ');
        break;
      case '2':
        var info = await this.model().query(' SELECT IFNULL(count(tb.guestid),0) as num FROM `think_bespeak` tb LEFT JOIN `think_or_app_user` au ON tb.userid = au.id WHERE tb.bespeaktime >= ' + starttime + ' AND tb.bespeaktime < ' + endtime + ' AND tb.type =2 AND au.bumenid = ' + jsonshop + ' GROUP BY tb.guestid ');
        break;
      case '3':
        if (ismanage == 1) {
          var info = await this.model().query(' SELECT IFNULL(count(tb.guestid),0) as num FROM `think_bespeak` tb LEFT JOIN `think_or_app_user` au ON tb.userid = au.id WHERE tb.bespeaktime >= ' + starttime + ' AND tb.bespeaktime < ' + endtime + ' AND tb.type =2 AND au.xiaozuid = ' + jsonshop + ' GROUP BY tb.guestid ');
        } else {
          var info = await this.model().query(' SELECT IFNULL(count(guestid),0) as num FROM `think_bespeak` WHERE bespeaktime >= ' + starttime + ' AND bespeaktime < ' + endtime + ' AND type =2 AND userid = ' + user_id + ' GROUP BY guestid ');
        }
        break;
    }

    if (think.isEmpty(info)) { return 0 } else { return info[0].num }
  }
  // -----------------------------------------------------------------------------业绩类通用接口
  // 获取获取规划业绩通用接口
  async getyejiplanxianjin(starttime, endtime, floor, jsonshop, ismanage, user_id) {
    switch (floor) {
      case '0':
      case '1':
        var info = await this.model().query(' SELECT IFNULL(sum(po.amount),0) as amount FROM `think_plan_order` po LEFT JOIN `think_or_app_user` au ON po.userid = au.id WHERE po.arrivetime >= ' + starttime + ' AND po.arrivetime < ' + endtime + ' AND au.shopid IN (' + jsonshop + ') ');
        break;
      case '2':
        var info = await this.model().query(' SELECT IFNULL(sum(po.amount),0) as amount FROM `think_plan_order` po LEFT JOIN `think_or_app_user` au ON po.userid = au.id WHERE po.arrivetime >= ' + starttime + ' AND po.arrivetime < ' + endtime + ' AND au.bumenid = ' + jsonshop + ' ');
        break;
      case '3':
        if (ismanage == 1) {
          var info = await this.model().query(' SELECT IFNULL(sum(po.amount),0) as amount FROM `think_plan_order` po LEFT JOIN `think_or_app_user` au ON po.userid = au.id WHERE po.arrivetime >= ' + starttime + ' AND po.arrivetime < ' + endtime + ' AND au.xiaozuid = ' + jsonshop + ' ');
        } else {
          var info = await this.model().query(' SELECT IFNULL(sum(amount),0) as amount FROM `think_plan_order` WHERE arrivetime >= ' + starttime + ' AND arrivetime < ' + endtime + ' AND userid = ' + user_id + ' ');
        }
        break;
    }

    if (think.isEmpty(info)) { return 0 } else { return info[0].amount }
  }
  // 获取现金业绩通用接口
  async getyejixianjin(starttime, endtime, floor, jsonshop, ismanage, user_id) {
    switch (floor) {
      case '0':
      case '1':
        var info = await this.model().query(' SELECT IFNULL(sum(osy.xianjinyeji),0) as amount FROM `think_or_shop_yejiticheng` osy LEFT JOIN `think_or_app_user` au ON osy.userid = au.id WHERE osy.addtime >= ' + starttime + ' AND osy.addtime < ' + endtime + ' AND au.shopid IN (' + jsonshop + ')');
        break;
      case '2':
        var info = await this.model().query(' SELECT IFNULL(sum(osy.xianjinyeji),0) as amount FROM `think_or_shop_yejiticheng` osy LEFT JOIN `think_or_app_user` au ON osy.userid = au.id WHERE osy.addtime >= ' + starttime + ' AND osy.addtime < ' + endtime + ' AND au.bumenid = ' + jsonshop + ' ');
        break;
      case '3':
        if (ismanage == 1) {
          var info = await this.model().query('SELECT IFNULL(sum(osy.xianjinyeji),0) as amount FROM `think_or_shop_yejiticheng` osy LEFT JOIN `think_or_app_user` au ON osy.userid = au.id WHERE osy.addtime >= ' + starttime + ' AND osy.addtime < ' + endtime + ' AND au.xiaozuid = ' + jsonshop + ' ');
        } else {
          var info = await this.model().query('SELECT IFNULL(sum(xianjinyeji),0) as amount FROM `think_or_shop_yejiticheng` WHERE addtime >= ' + starttime + ' AND addtime < ' + endtime + ' AND userid = ' + user_id + ' ');
        }
        break;
    }
    if (think.isEmpty(info)) { return 0 } else { return info[0].amount }
  }
  // 获取卡耗业绩通用接口
  async getyejikahao(starttime, endtime, floor, jsonshop, ismanage, user_id) {
    switch (floor) {
      case '0':
      case '1':
        var info = await this.model().query(' SELECT IFNULL(sum(osy.kahaoyeji),0) as amount FROM `think_or_shop_yejiticheng` osy LEFT JOIN `think_or_app_user` au ON osy.userid = au.id WHERE osy.addtime >= ' + starttime + ' AND osy.addtime < ' + endtime + ' AND au.shopid IN (' + jsonshop + ')');
        break;
      case '2':
        var info = await this.model().query(' SELECT IFNULL(sum(osy.kahaoyeji),0) as amount FROM `think_or_shop_yejiticheng` osy LEFT JOIN `think_or_app_user` au ON osy.userid = au.id WHERE osy.addtime >= ' + starttime + ' AND osy.addtime < ' + endtime + ' AND au.bumenid = ' + jsonshop + ' ');
        break;
      case '3':
        if (ismanage == 1) {
          var info = await this.model().query('SELECT IFNULL(sum(osy.kahaoyeji),0) as amount FROM `think_or_shop_yejiticheng` osy LEFT JOIN `think_or_app_user` au ON osy.userid = au.id WHERE osy.addtime >= ' + starttime + ' AND osy.addtime < ' + endtime + ' AND au.xiaozuid = ' + jsonshop + ' ');
        } else {
          var info = await this.model().query('SELECT IFNULL(sum(kahaoyeji),0) as amount FROM `think_or_shop_yejiticheng` WHERE addtime >= ' + starttime + ' AND addtime < ' + endtime + ' AND userid = ' + user_id + ' ');
        }
        break;
    }
    if (think.isEmpty(info)) { return 0 } else { return info[0].amount }
  }
  // bottom
  // 获取分店列表
  async getsystemshoplist(orcode) {
    return this.model('or_department').where({orcode: orcode, floor: 1}).select();
  }
  // 获取员工数量
  async getshopworknum(shopid) {
    const userNum = await this.model('or_app_user').where({shopid: shopid, isdelete: 0, isdisable: 0}).count();
    return userNum;
  }
  // 获取所有客户的数量
  async getshopallguestnum(shopid, isdisable) {
    const where = {};
    where.departmentid = shopid;
    if (isdisable) {
      where.isdisable = isdisable;
    }
    const guestNum = await this.model('or_guest').where(where).count();
    return guestNum;
  }

  // 获取营业收入
  async getYingYeShouRu(filter) {
    const res = await this.model('or_shop_order').where({status: ['!=', 0], departmentid: filter.shopid, addtime: {'>=': filter.beginTime, '<=': filter.endTime}}).sum('recemoney');
    return res;
  }

  async getQiTaShouRu(filter) {
    const res = await this.model('or_shop_cost').where({type: filter.type, shopid: filter.shopid, addtime: {'>=': filter.beginTime, '<=': filter.endTime}}).sum('costmoney');
    return res;
  }
  // 获取品牌下的商品数量
  async getbrandinfo(brandid, shopid) {
  	const num = await this.model('or_shop_goods').alias('sg').join('think_or_goods g on sg.goodsid = g.id').where({'g.brandid': brandid, 'sg.shopid': shopid}).count();
  	return num || 0;
  }

  // 获取项目数量
  async getbrandsubjectnum(brandid, shopid) {
  	const num = await this.model('or_shop_subject').alias('sb').join('think_or_subject s on sb.subjectid = s.id').where({'s.brandid': brandid, 'sb.shopid': shopid}).count();
  	return num || 0;
  }
  // 获取产品损耗
  async getSunHao(filter) {
  	const res = await this.model('or_shop_goodslog').alias('gl').join('think_or_goods g on gl.goodsid = g.id').where({'gl.departmentid': filter.shopid, 'gl.addtime': {'>=': filter.beginTime, '<=': filter.endTime}, 'gl.ordertype': ['NOTIN', ['RKD', 'CKD']]}).sum('gl.nums * g.shopprice');
  	return think.isEmpty(res) ? 0 : res;
  }
};
